package fr.esiag.isidis.epp.search;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import fr.esiag.isidis.epp.generatedClasses.Query;

@Repository
public class PointCdcRepository {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
public List<PointCdc> individualQuery(Query inquery){
		
		String id;
		int dateDebut;
		int heureDebut;
		int dateFin;
		int heureFin;
		
		id = inquery.getIDSite();
		dateDebut = inquery.getDateDebut();
		heureDebut = inquery.getHeureDebut();
		dateFin = inquery.getDateFin();
		heureFin = inquery.getHeureFin();
		
		
		String  sql = 	
				"SELECT tdate, thour , power, temprature, weight, Temperature.idsite "+
				" FROM Temperature,Power_Weight "+
				" WHERE Temperature.idsite = Power_weight.idsite "+
				" AND tdate = pdate "+
				" AND thour = phour "+
				" AND tdate >= '"+ dateDebut +
				"' AND tdate <= '"+ dateFin +
				"' AND thour >= '"+ heureDebut +
				"' AND thour <= '"+ heureFin +
				"' AND temperature.idsite = '" + id + "'";	
		
		RowMapper row = new PointCdcRowMapperindividual();
		List<PointCdc> pdcs = jdbcTemplate.query(sql, row);
		return pdcs;
	}
	
public List<PointCdc> cumulQuery(Query inquery){
		
		String id;
		int dateDebut;
		int heureDebut;
		int dateFin;
		int heureFin;
		
		id = inquery.getIDSite();
		dateDebut = inquery.getDateDebut();
		heureDebut = inquery.getHeureDebut();
		dateFin = inquery.getDateFin();
		heureFin = inquery.getHeureFin();
		
		
		String  sql = 	
				"SELECT tdate, thour , SUM(power), SUM(temprature), SUM(weight), Temperature.idsite "+
				" FROM Temperature,Power_Weight "+
				" WHERE Temperature.idsite = Power_weight.idsite "+
				" AND tdate = pdate "+
				" AND thour = phour "+
				" AND tdate >= '"+ dateDebut +
				"' AND tdate <= '"+ dateFin +
				"' AND thour >= '"+ heureDebut +
				"' AND thour <= '"+ heureFin +
				"' AND temperature.idsite = '" + id + "'";	
		
		RowMapper row = new PointCdcRowMappercumul();
		List<PointCdc> pdcs = jdbcTemplate.query(sql, row);
		return pdcs;
	}
	

public List<PointCdc> moyenneQuery(Query inquery){
	
	String id;
	int dateDebut;
	int heureDebut;
	int dateFin;
	int heureFin;
	
	id = inquery.getIDSite();
	dateDebut = inquery.getDateDebut();
	heureDebut = inquery.getHeureDebut();
	dateFin = inquery.getDateFin();
	heureFin = inquery.getHeureFin();
	
	
	String  sql = 	
			"SELECT tdate, thour , AVG(power), AVG(temprature), AVG(weight), Temperature.idsite "+
			" FROM Temperature,Power_Weight "+
			" WHERE Temperature.idsite = Power_weight.idsite "+
			" AND tdate = pdate "+
			" AND thour = phour "+
			" AND tdate >= '"+ dateDebut +
			"' AND tdate <= '"+ dateFin +
			"' AND thour >= '"+ heureDebut +
			"' AND thour <= '"+ heureFin +
			"' AND temperature.idsite = '" + id + "'";	
	
	RowMapper row = new PointCdcRowMappermoyenne();
	List<PointCdc> pdcs = jdbcTemplate.query(sql, row);
	return pdcs;
}
}
